import json
# from icecream import ic
import pandas as pd
from openpyxl.styles import Alignment, Font
from openpyxl.styles import PatternFill
from openpyxl import Workbook, load_workbook
from datetime import datetime
from openpyxl.utils import get_column_letter
from openpyxl.styles import Border, Side

from gen_info_dic import gen_info_dic


class Color:
    def __init__(self):
        self.gray = PatternFill(
            start_color='C0C0C0',
            end_color='C0C0C0',
            fill_type='solid')
        self.blue = PatternFill(
            start_color='D9E1F2',
            end_color='D9E1F2',
            fill_type='solid')
        self.yellow = PatternFill(
            start_color='FFFF00',
            end_color='FFFF00',
            fill_type='solid')
        self.green = PatternFill(
            start_color='E2EFDA',
            end_color='E2EFDA',
            fill_type='solid')


border_style = Border(
    left=Side(border_style='thin', color='000000'),
    right=Side(border_style='thin', color='000000'),
    top=Side(border_style='thin', color='000000'),
    bottom=Side(border_style='thin', color='000000')
)

def decorate_Summary(existing_wb, info_dic):
    color = Color()
    sheet = existing_wb['Summary']
    row_num = info_dic['df_info']['Summary']['row_num']
    col_num = info_dic['df_info']['Summary']['col_num']
    rs_count_loc = info_dic['df_info']['Summary']['rs_count_loc']
    Output_loc = info_dic['df_info']['Summary']['Output_loc']
    # ic(rs_count_loc)
    for col in range(col_num):
        col = col + 1
        header_cell = sheet.cell(row=1, column=col)
        header_cell.fill = color.gray
        header_cell.font = Font(name='宋体', size=14, bold=True)
        # 对其方式
        header_cell.alignment = Alignment(
            horizontal='center',
            vertical='center',
            wrap_text=True)
        column_letter = get_column_letter(col)  # 将列索引1转换为字母表示
        sheet.column_dimensions[column_letter].width = 20
        if col >= Output_loc + 1:
            cell = sheet.cell(row=row_num , column=col)
            cell.fill = color.green
    for col in range(rs_count_loc, col_num):
        col = col + 1
        if (col == rs_count_loc + 2) or (col == rs_count_loc + 3) or (col == rs_count_loc+4):
            continue
        for row in range(row_num):
            row = row + 1
            if row == 1:
                continue
            cell = sheet.cell(row=row, column=col)
            if type(cell.value) == int:
                if row!=row_num:
                    if cell.value>0:
                        cell.fill = color.yellow
                    if col > rs_count_loc+4:
                        alg_loc=col - (rs_count_loc+4)
                        link_col = 7 + alg_loc
                        link_row = row
                        link_col_letter = get_column_letter(link_col)
                        cell.value = f'=HYPERLINK("#MAP!{link_col_letter}{link_row}","{cell.value}")'
                        cell.alignment = Alignment(horizontal='right')
                        MAP = existing_wb['MAP']
                        MAP.cell(row=link_row, column=link_col).value='flag'


def decorate_MAP(existing_wb, info_dic):
    color = Color()
    sheet = existing_wb['MAP']
    row_num = info_dic['df_info']['MAP']['row_num']
    col_num = info_dic['df_info']['MAP']['col_num']
    for col in range(col_num):
        col += 1
        column_letter = get_column_letter(col)  # 将列索引1转换为字母表示
        if col <= 3:
            sheet.column_dimensions[column_letter].width = 8.38
        else:
            sheet.column_dimensions[column_letter].width = 40
    for row in range(row_num):
        row += 1
        if row == 1:
            sheet.row_dimensions[row].height = 37.5
        else:
            sheet.row_dimensions[row].height = 160
    for col in range(col_num):
        col += 1
        for row in range(row_num):
            row += 1
            cell = sheet.cell(row=row, column=col)
            if row == 1:
                cell.fill = color.gray
                cell.font = Font(name='宋体', size=14, bold=True)
            cell.alignment = Alignment(
                horizontal='center',
                vertical='center',
                wrap_text=True)


def decorate_RsRuleSummary(existing_wb, info_dic):
    color = Color()
    sheet = existing_wb['RsRuleSummary']
    row_num = info_dic['df_info']['RsRuleSummary']['row_num']
    col_num = info_dic['df_info']['RsRuleSummary']['col_num']

    sheet.row_dimensions[1].height = 52.5
    for col in range(col_num):
        col += 1
        cell = sheet.cell(row=1, column=col)
        cell.fill = color.gray
        cell.font = Font(name='宋体', size=14, bold=True)
        # 对其方式
        cell.alignment = Alignment(
            horizontal='center',
            vertical='center',
            wrap_text=True)
        column_letter = get_column_letter(col)  # 将列索引1转换为字母表示
        sheet.column_dimensions[column_letter].width = 20


def decorate_RsDieSummary(existing_wb, info_dic):
    color = Color()
    sheet = existing_wb['RsDieSummary']
    row_num = info_dic['df_info']['RsDieSummary']['row_num']
    col_num = info_dic['df_info']['RsDieSummary']['col_num']

    sheet.row_dimensions[1].height = 52.5
    for col in range(col_num):
        col += 1
        cell = sheet.cell(row=1, column=col)
        cell.fill = color.gray
        cell.font = Font(name='宋体', size=14, bold=True)
        # 对其方式
        cell.alignment = Alignment(
            horizontal='center',
            vertical='center',
            wrap_text=True)
        column_letter = get_column_letter(col)  # 将列索引1转换为字母表示
        sheet.column_dimensions[column_letter].width = 20


def decorate_base_sheet(re_file_path, info_dic):
    existing_wb = load_workbook(re_file_path)
    # decorate_headers(existing_wb, info_dic)
    decorate_Summary(existing_wb, info_dic)
    decorate_MAP(existing_wb, info_dic)
    decorate_RsRuleSummary(existing_wb, info_dic)
    decorate_RsDieSummary(existing_wb, info_dic)
    existing_wb.save(re_file_path)


if __name__ == '__main__':
    from get_sql_res import gen_run_detail
    rule_run_record_id = 7088
    df_run_detail = gen_run_detail(rule_run_record_id)
    current_time = datetime.now().strftime("%m_%d_%H_%M")
    re_file_path = f"res/file_{current_time}.xlsx"
    sheet = 'Summary'
    info_dic = gen_info_dic(df_run_detail)
    ##
    write_in(rule_run_record_id, re_file_path)
    decorate(re_file_path, sheet, info_dic)

